<?php
class Record_model extends CI_Model {
	
	public function add_record($user_id, $memory, $description, $tag)
	{
		$sql = 'INSERT INTO record (userId, memory, description, tag, addTime, month) VALUES (?, ?, ?, ?, ?, ?)';
		$query = $this->db->query($sql, array($user_id, $memory, $description, $tag, date('Y-m-d H:i:s'), date('Y-m')));

		return $this->db->affected_rows() > 0 ? $this->db->insert_id() : false;
	}
	
	public function update_record($record_id, $memory, $description, $tag)
	{
		$sql = 'UPDATE record SET memory=?, description=?, tag=? WHERE recordId=? LIMIT 1';
		$query = $this->db->query($sql, array($memory, $description, $tag, $record_id));

		return $this->db->affected_rows() > 0;
	}
	
	public function del_record($record_id)
	{
		$sql = 'DELETE FROM record WHERE recordId=? LIMIT 1';
		$query = $this->db->query($sql, array($record_id));

		return $this->db->affected_rows() > 0;
	}
	
	public function get_record_by_id($record_id)
	{
		$sql = 'SELECT * FROM record WHERE recordId=? LIMIT 1';
		$query = $this->db->query($sql, array($record_id));
		
		return $query->num_rows > 0 ? $query->row() : false;
	}
	
	public function change_record_tag($user_id, $old_tag, $new_tag)
	{
		$sql = 'UPDATE record SET tag=? WHERE userId=? AND tag=?';
		$query = $this->db->query($sql, array($new_tag, $user_id, $old_tag));
		
		return $this->db->affected_rows() > 0;
	}
	
	//get month list in which user add record
	public function get_month_list($user_id)
	{
		$sql = 'SELECT DISTINCT(month) AS month FROM record WHERE userId=?';
		$query = $this->db->query($sql, array($user_id));

		return $query->result();
	}
	
	public function get_list_count_by_time($user_id, $from_time, $to_time)
	{
		$sql = 'SELECT COUNT(*) AS c FROM record WHERE userId=? AND addTime>=? AND addTime<=?';
		$query = $this->db->query($sql, array($user_id, $from_time, $to_time));
		
		return $query->row()->c;
	}
	
	public function get_list_by_time($user_id, $from_time, $to_time, $from)
	{
		$sql = 'SELECT * FROM record WHERE userId=? AND addTime>=? AND addTime<=? ORDER BY addTime DESC LIMIT ?, ?';
		$query = $this->db->query($sql, array($user_id, $from_time, $to_time, $from, Variable::LIST_SIZE));
		
		return $query->result();
	}
	
	public function get_list_count_by_tag($user_id, $tag_name)
	{
		$sql = 'SELECT COUNT(*) AS c FROM record WHERE userId=? AND tag=?';
		$query = $this->db->query($sql, array($user_id, $tag_name));
		
		return $query->row()->c;
	}
	
	public function get_list_by_tag($user_id, $tag_name, $from)
	{
		$sql = 'SELECT * FROM record WHERE userId=? AND tag=? ORDER BY addTime DESC LIMIT ?, ?';	
		$query = $this->db->query($sql, array($user_id, $tag_name, $from, Variable::LIST_SIZE));
		
		return $query->result();
	}
}
?>